﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Configuration.Provider;
using System.Data.Common;
using System.Net.Mail;
using System.Web.Hosting;

namespace Altairis.MailToolkit {

    public class SqlMailingListProvider : MailingListProvider {
        private const string DEFAULT_TABLE_NAME = "MailingLists";
        private const string DEFAULT_LIST_NAME_COLUMN = "ListName";
        private const string DEFAULT_EMAIL_ADDRESS_COLUMN = "EmailAddress";
        private const string DEFAULT_DATE_CONFIRMED_COLUMN = "DateConfirmed";
        private const string DEFAULT_IP_ADDRESS_COLUMN = "IpAddress";

        private ConnectionStringSettings connectionString;

        // Initialization and configuration

        /// <summary>
        /// Initializes the specified name.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="config">The config.</param>
        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config) {
            // Base init
            base.Initialize(name, config);

            // Read connection string
            this.ConnectionStringName = config.GetConfigValue("connectionStringName", null);
            if (string.IsNullOrWhiteSpace(this.ConnectionStringName)) throw new ConfigurationErrorsException("Required \"connectionStringName\" attribute not specified.");
            this.connectionString = ConfigurationManager.ConnectionStrings[this.ConnectionStringName];
            if (this.connectionString == null) throw new ConfigurationErrorsException(string.Format("Connection string \"{0}\" was not found.", this.ConnectionStringName));
            if (string.IsNullOrEmpty(this.connectionString.ProviderName)) throw new ConfigurationErrorsException(string.Format("Connection string \"{0}\" does not have specified the \"providerName\" attribute.", this.ConnectionStringName));

            // Read other configuration
            this.TableName = config.GetConfigValue("tableName", DEFAULT_TABLE_NAME);
            this.EmailAddressColumn = config.GetConfigValue("emailAddressColumn", DEFAULT_EMAIL_ADDRESS_COLUMN);
            this.ListNameColumn = config.GetConfigValue("listNameColumn", DEFAULT_LIST_NAME_COLUMN);
            this.DateConfirmedColumn = config.GetConfigValue("dateConfirmedColumn", DEFAULT_DATE_CONFIRMED_COLUMN);
            this.IpAddressColumn = config.GetConfigValue("ipAddressColumn", DEFAULT_IP_ADDRESS_COLUMN);
            this.UseExtendedProperties = config.GetConfigValue("useExtendedProperties", false);

            // Throw error on excess attributes
            if (config.Count != 0) throw new ConfigurationErrorsException("Unrecognized configuration attributes found: " + string.Join(", ", config.AllKeys));
        }

        /// <summary>
        /// Gets the name of the connection string.
        /// </summary>
        /// <value>
        /// The name of the connection string.
        /// </value>
        public string ConnectionStringName { get; private set; }

        /// <summary>
        /// Gets the name of the table.
        /// </summary>
        /// <value>
        /// The name of the table.
        /// </value>
        public string TableName { get; private set; }

        /// <summary>
        /// Gets the list name column.
        /// </summary>
        /// <value>
        /// The list name column.
        /// </value>
        public string ListNameColumn { get; private set; }

        /// <summary>
        /// Gets the email address column.
        /// </summary>
        /// <value>
        /// The email address column.
        /// </value>
        public string EmailAddressColumn { get; private set; }

        /// <summary>
        /// Gets the date confirmed column.
        /// </summary>
        /// <value>
        /// The date confirmed column.
        /// </value>
        public string DateConfirmedColumn { get; private set; }

        /// <summary>
        /// Gets the ip address column.
        /// </summary>
        /// <value>
        /// The ip address column.
        /// </value>
        public string IpAddressColumn { get; private set; }

        /// <summary>
        /// Gets a value indicating whether the confirmation evidence subsystem is to be used.
        /// </summary>
        /// <value>
        /// <c>true</c> if confirmation evidence is supported; otherwise, <c>false</c>.
        /// </value>
        public bool UseExtendedProperties { get; private set; }

        // Provider implementation

        /// <summary>
        /// Subscribes the e-mail address to mailing list, without confirmation.
        /// </summary>
        /// <param name="emailAddress">The e-mail address.</param>
        /// <returns>
        /// Returns <c>true</c> when subscription succeeded or <c>false</c> when e-mail is already in list.
        /// </returns>
        /// <exception cref="System.ArgumentNullException">The emailAddress is null</exception>
        /// <exception cref="System.Configuration.Provider.ProviderException">Error while performing database operation</exception>
        public override bool SubscribeForce(MailAddress emailAddress) {
            // Validate arguments
            if (emailAddress == null) throw new ArgumentNullException("emailAddress");
            if (this.IsMember(emailAddress)) return false; // Already in list

            var sqlCommand = "INSERT INTO $Table ($ListName, $EmailAddress) VALUES (@ListName, @EmailAddress)";
            var ipAddress = string.Empty;

            if (this.UseExtendedProperties) {
                sqlCommand = "INSERT INTO $Table ($ListName, $EmailAddress, $DateConfirmed, $IpAddress) VALUES (@ListName, @EmailAddress, @DateConfirmed, @IpAddress)";
                if (System.Web.HttpContext.Current != null) ipAddress = System.Web.HttpContext.Current.Request.UserHostAddress;
            }

            // Add to mailing list
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand(sqlCommand, db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@ListName", this.Name.ToLower());
                    cmd.AddParameterWithValue("@EmailAddress", emailAddress.Address.ToLower());
                    if (this.UseExtendedProperties) {
                        cmd.AddParameterWithValue("@DateConfirmed", DateTime.Now);
                        cmd.AddParameterWithValue("@IpAddress", ipAddress);
                    }

                    // Execute command
                    db.Open();
                    cmd.ExecuteNonQuery();
                    db.Close();
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
            return true;
        }

        /// <summary>
        /// Removes the e-mail address from mailing list, without confirmation.
        /// </summary>
        /// <param name="emailAddress">The e-mail address.</param>
        /// <returns>
        /// Returns <c>true</c> when removal succeeded or <c>false</c> when e-mail is not in list.
        /// </returns>
        /// <exception cref="System.ArgumentNullException">The emailAddress is null</exception>
        /// <exception cref="System.Configuration.Provider.ProviderException">Error while performing database operation</exception>
        public override bool RemoveForce(MailAddress emailAddress) {
            // Validate arguments
            if (emailAddress == null) throw new ArgumentNullException("emailAddress");
            if (!this.IsMember(emailAddress)) return false; // Already not in list

            // Add to mailing list
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("DELETE FROM $Table WHERE $ListName=@ListName AND $EmailAddress=@EmailAddress", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@ListName", this.Name.ToLower());
                    cmd.AddParameterWithValue("@EmailAddress", emailAddress.Address.ToLower());

                    // Execute command
                    db.Open();
                    cmd.ExecuteNonQuery();
                    db.Close();
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
            return true;
        }

        /// <summary>
        /// Gets the number of list members.
        /// </summary>
        /// <returns>
        /// Number of list members.
        /// </returns>
        /// <exception cref="System.Configuration.Provider.ProviderException">Error while performing database operation</exception>
        public override int GetMemberCount() {
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("SELECT COUNT(*) FROM $Table WHERE $ListName=@ListName", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@ListName", this.Name.ToLower());

                    // Execute command
                    db.Open();
                    var count = (int)cmd.ExecuteScalar();
                    db.Close();
                    return count;
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        /// <summary>
        /// Gets the list of member e-mail addresses.
        /// </summary>
        /// <returns>
        /// List of member e-mail addresses.
        /// </returns>
        /// <exception cref="System.Configuration.Provider.ProviderException">Error while performing database operation</exception>
        public override IEnumerable<string> GetMembers() {
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("SELECT $EmailAddress FROM $Table WHERE $ListName=@ListName ORDER BY $EmailAddress", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@ListName", this.Name.ToLower());

                    // Execute command
                    db.Open();
                    var recipients = new List<string>();
                    using (var r = cmd.ExecuteReader()) {
                        while (r.Read()) {
                            recipients.Add(r[0] as string);
                        }
                    }
                    db.Close();
                    return recipients;
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        /// <summary>
        /// Determines whether the specified email address is member of this list.
        /// </summary>
        /// <param name="emailAddress">The email address.</param>
        /// <returns>
        ///   <c>true</c> if the specified email address is member; otherwise, <c>false</c>.
        /// </returns>
        /// <exception cref="System.ArgumentNullException">The emailAddress is null.</exception>
        /// <exception cref="System.Configuration.Provider.ProviderException">Error while performing database operation</exception>
        public override bool IsMember(MailAddress emailAddress) {
            if (emailAddress == null) throw new ArgumentNullException("emailAddress");

            // Check if we have this e-mail address in list
            try {
                using (HostingEnvironment.Impersonate())
                using (var db = this.connectionString.CreateDbConnection())
                using (var cmd = this.CreateDbCommand("SELECT COUNT(*) FROM $Table WHERE $ListName=@ListName AND $EmailAddress=@EmailAddress", db)) {
                    // Prepare command
                    cmd.AddParameterWithValue("@ListName", this.Name.ToLower());
                    cmd.AddParameterWithValue("@EmailAddress", emailAddress.Address.ToLower());

                    // Execute command
                    db.Open();
                    var count = (int)cmd.ExecuteScalar();
                    db.Close();
                    return count == 1;
                }
            }
            catch (Exception ex) {
                throw new ProviderException("Error while performing database operation", ex);
            }
        }

        // Helper methods

        private DbCommand CreateDbCommand(string commandText, DbConnection db) {
            var cmd = db.CreateCommand();
            cmd.CommandText = commandText.Replace("$Table", this.TableName);
            cmd.CommandText = cmd.CommandText.Replace("$ListName", this.ListNameColumn);
            cmd.CommandText = cmd.CommandText.Replace("$EmailAddress", this.EmailAddressColumn);
            cmd.CommandText = cmd.CommandText.Replace("$DateConfirmed", this.DateConfirmedColumn);
            cmd.CommandText = cmd.CommandText.Replace("$IpAddress", this.IpAddressColumn);
            return cmd;
        }
    }
}